cd "S:\Katja_Aksel\JFQA index paper\v3\pseudo_data"
clear
set more 1
cap log close

log using ./logs/Figures4_5.log, replace

use ./6_investors.dta, clear
***identify only VC equity
keep if investortype==5
keep orgnr time primamount
rename primamount vcamount
save ./helpfile_vcamounts.dta, replace

use ./5_postmoney_valuations_failures.dta, clear
sort orgnr time
**identify number of actual rounds (disregard the fictive zero/failed rounds)
**keep only actual valuation observations
keep if mv_rounds!=. & mv_rounds!=0
sort orgnr time
bys orgnr: gen total_rounds=_N
keep orgnr total_rounds
duplicates drop
sum total
*merge back to the file
merge 1:m orgnr using ./5_postmoney_valuations_failures.dta
replace total_rounds=0 if _merge==2
drop _merge
*gen variable for multiround firms
gen multiround=(total_rounds>1)

**new variable cumulative equity raised
sort orgnr time
bys orgnr: gen lauf=_n
replace primamount=0 if primamount==.
**start with the initial legally required equity+founder's initial equity as the first obseration
gen cumequity=initial if lauf==1
replace cumequity=100000 if lauf==1 & founded<2012 & (cumequity==. | cumequity<100000)
replace cumequity=30000 if lauf==1 & founded>=2012 & (cumequity==. | cumequity<30000)
*add equity additionally raised in the first observation
replace cumequity=cumequity+primamount if lauf==1
*roll over
bys orgnr: replace cumequity=cumequity[_n-1]+primamount if lauf>1
drop lauf
sum cumequity, d
/*
                          cumequity
-------------------------------------------------------------
      Percentiles      Smallest
 1%        30000          30000
 5%        30000          30000
10%        30000          30000       Obs             147,536
25%       100000          30000       Sum of wgt.     147,536

50%       173700                      Mean            9891653
                        Largest       Std. dev.      3.02e+08
75%       575000       2.71e+10
90%      3114180       3.71e+10       Variance       9.15e+16
95%     1.14e+07       5.13e+10       Skewness       142.0735
99%     1.39e+08       6.90e+10       Kurtosis       26436.07

*/

**merge in vc amounts
merge 1:1 orgnr time using ./helpfile_vcamounts.dta
drop if _merge==2
replace vcamount=0 if _merge==1
drop _merge
sum vcamount, d
/*
                         vcamount
-------------------------------------------------------------
      Percentiles      Smallest
 1%            0              0
 5%            0              0
10%            0              0       Obs             147,536
25%            0              0       Sum of wgt.     147,536

50%            0                      Mean           231641.1
                        Largest       Std. dev.       7766943
75%            0       7.38e+08
90%            0       7.68e+08       Variance       6.03e+13
95%            0       9.91e+08       Skewness       82.49707
99%       375000       1.28e+09       Kurtosis       9443.426
*/

**generate new variable cumulative non-VC-equity
sort orgnr time
bys orgnr: gen lauf=_n
**start with the initial legally required equity+founder's initial equity as the first obseration
gen nonVCcumequity=initial if lauf==1
replace nonVCcumequity=100000 if lauf==1 & founded<2012 & (nonVCcumequity==. | nonVCcumequity<100000)
replace nonVCcumequity=30000 if lauf==1 & founded>=2012 & (nonVCcumequity==. | nonVCcumequity<30000)
*add equity additionally raised in the first observation
replace nonVCcumequity=nonVCcumequity+primamount-vcamount if lauf==1
*roll over
bys orgnr: replace nonVCcumequity=nonVCcumequity[_n-1]+primamount-vcamount if lauf>1
drop lauf
sum cumequity, d
sum nonVCcumequity, d
replace nonVCcumequity=30000 if nonVCcumequity<0
*3 changes

****generate firm age variable
merge m:1 orgnr using ./1n_founding_dates.dta
keep if _merge==3
drop _merge
***some firms in 2010 do not have the specific registration/founding date, we drop those observations
drop if ftime==.
gen firmage=time-ftime+1
order orgnr time vcbacked multiround firmage

**aggregate valuation observations before the founding date
replace firmage=0 if firmage<0
**aggregate valuation observations older than 10 years
replace firmage=120 if firmage>120

**keep only HIP firms - this is our baseline
keep if sample==1

*winsorize relevant variables at 99.5% percentile and generate values in million
foreach t in mv_rounds mv_all cumequity nonVCcumequity {
sum `t', d
return list
sum `t' if `t'>r(p99), d
return list
replace `t'=r(p50) if `t'>r(p50) & `t'!=.
replace `t'=`t'/1000000
}
save ./static_analyses.dta, replace


****Figures 4 and 5
foreach var of varlist mv_rounds mv_all {
use ./static_analyses.dta, clear
**generate interaction variables of firm type and firm age
gen vcage=vcbacked*firmage
gen multiage=multiround*firmage

**generate tables and figures for valuations based on financing rounds only and based on all valuations
***********************************************************
***Figure 4: VC premium regressions for table outputs (online appendix) and for figures
**blue line
reg `var' i.vcbacked i.firmage i.vcage
*online appendix
outreg2 using ./figures/Figure4.xls, bdec(3) label ctitle(`var') append  keep (i.vcbacked i.vcage) addtext(Industry FE, No, Calendar Month FE, No, Equity Raised, No)
*figure
outreg2 using ./figures/Figure4`var'.txt, bdec(3) replace keep (i.vcbacked i.vcage) noaster stats(coef) nonotes nocons nor2 noobs

**red line
reg `var' i.vcbacked i.firmage i.vcage i.industry
outreg2 using ./figures/Figure4.xls, bdec(3) label ctitle(`var') append keep (i.vcbacked i.vcage) addtext(Industry FE, Yes, Calendar Month FE, No, Equity Raised, No)
outreg2 using ./figures/Figure4`var'.txt, bdec(3)  append keep (i.vcbacked i.vcage) noaster stats(coef) nonotes nocons nor2 noobs

**green line
reg `var' i.vcbacked i.firmage i.vcage i.industry i.time
outreg2 using ./figures/Figure4.xls, bdec(3) label ctitle(`var') append keep (i.vcbacked i.vcage) addtext(Industry FE, Yes, Calendar Month FE, Yes, Equity Raised, No)
outreg2 using ./figures/Figure4`var'.txt, bdec(3) append keep (i.vcbacked i.vcage) noaster stats(coef) nonotes nocons nor2 noobs

**yellow line
reg `var' i.vcbacked i.firmage i.vcage c.nonVCcumequity i.industry i.time
outreg2 using ./figures/Figure4.xls, bdec(3) label ctitle(`var') append keep (i.vcbacked i.vcage) addtext(Industry FE, Yes, Calendar Month FE, Yes, Equity Raised, Yes)
outreg2 using ./figures/Figure4`var'.txt, bdec(3) append keep (i.vcbacked i.vcage) noaster stats(coef) nonotes nocons nor2 noobs

**purple line
reg `var' i.vcbacked i.firmage i.vcage c.cumequity i.industry i.time
outreg2 using ./figures/Figure4.xls, bdec(3) label ctitle(`var') append keep (i.vcbacked i.vcage) addtext(Industry FE, Yes, Calendar Month FE, Yes, Equity Raised, Yes)
outreg2 using ./figures/Figure4`var'.txt, bdec(3) append keep (i.vcbacked i.vcage) noaster stats(coef) nonotes nocons nor2 noobs

***Figure 5: Multiround premium regressions for table outputs (online appendix) and for figures
**blue line
reg `var' i.multiround i.firmage i.multiage
*online appendix
outreg2 using ./figures/Figure5.xls, bdec(3) label ctitle(`var') append keep (i.multiround i.multiage) addtext(Industry FE, No, Calendar Month FE, No, Equity Raised, No)
outreg2 using ./figures/Figure5`var'.txt, bdec(3) replace keep (i.multiround i.multiage) noaster stats(coef) nonotes nocons nor2 noobs

**red line
reg `var' i.multiround i.firmage i.multiage i.industry
outreg2 using ./figures/Figure5.xls, bdec(3) label ctitle(`var') append keep (i.multiround i.multiage) addtext(Industry FE, Yes, Calendar Month FE, No, Equity Raised, No)
outreg2 using ./figures/Figure5`var'.txt, bdec(3)  append keep (i.multiround i.multiage) noaster stats(coef) nonotes nocons nor2 noobs

**green line
reg `var' i.multiround i.firmage i.multiage i.industry i.time
outreg2 using ./figures/Figure5.xls, bdec(3) label ctitle(`var') append keep (i.multiround i.multiage) addtext(Industry FE, Yes, Calendar Month FE, Yes, Equity Raised, No)
outreg2 using ./figures/Figure5`var'.txt, bdec(3) append keep (i.multiround i.multiage) noaster stats(coef)  nonotes nocons nor2 noobs

**yellow line
reg `var' i.multiround i.firmage i.multiage c.cumequity i.industry i.time
outreg2 using ./figures/Figure5.xls, bdec(3) label ctitle(`var') append keep (i.multiround i.multiage) addtext(Industry FE, Yes, Calendar Month FE, Yes, Equity Raised, Yes)
outreg2 using ./figures/Figure5`var'.txt, bdec(3) append keep (i.multiround i.multiage) noaster stats(coef) nonotes nocons nor2 noobs

***Create Figure 4
import delimited ./figures/Figure4`var'.txt, clear
drop in 1/3
drop if v1==""
destring, ignore (",") replace
gen firmage=.
forvalues i = 1(1)120 {
replace firmage=`i' if v1=="`i'.vcage"
}
replace firmage=0 if firmage==.

forvalues i = 2(1)6 {
gen help`i'=v`i' if v1=="1.vcbacked"
egen help1`i'=min(help`i')
gen index`i'=help1`i'+v`i' if firmage>0
replace index`i'=help1`i' if firmage==0
}

drop help*
keep firmage index*
twoway (line index2 firmage) (line index3 firmage) (line index4 firmage) (line index5 firmage) (line index6 firmage), ytitle(VC Valuation Premium) xtitle(Firm Age) graphregion(color(white)) xlabel(0 "0" 12 "1" 24 "2" 36 "3" 48 "4" 60 "5" 72 "6" 84 "7" 96 "8" 108 "9" 120 ">=10") ylabel(-200(50)200) yline(0) legend(order(1 "Total VC premium" 2 "Industry FE" 3 "Industry FE + Calendar month FE" 4 "Industry FE + Calendar month FE + Non-VC equity raised" 5 "Industry FE + Calendar month FE + Total equity raised") position(6) region(lcolor(white))) 
graph export ./figures/Figure4_`var'.png, replace

***Create Figure 5
import delimited ./figures/Figure5`var'.txt, clear
drop in 1/3
drop if v1==""
destring, ignore (",") replace
gen firmage=.
forvalues i = 1(1)120 {
replace firmage=`i' if v1=="`i'.multiage"
}
replace firmage=0 if firmage==.

forvalues i = 2(1)5 {
gen help`i'=v`i' if v1=="1.multiround"
egen help1`i'=min(help`i')
gen index`i'=help1`i'+v`i' if firmage>0
replace index`i'=help1`i' if firmage==0
}
drop help*
keep firmage index*
twoway (line index2 firmage) (line index3 firmage) (line index4 firmage) (line index5 firmage), ytitle(Multiround Valuation Premium) xtitle(Firm Age) graphregion(color(white)) xlabel(0 "0" 12 "1" 24 "2" 36 "3" 48 "4" 60 "5" 72 "6" 84 "7" 96 "8" 108 "9" 120 ">=10") ylabel(-50(50)100) yline(0) legend(order(1 "Total multiround premium" 2 "Industry FE" 3 "Industry FE + Calendar month FE" 4 "Industry FE + Calendar month FE + Total equity raised") position(6) region(lcolor(white))) 
graph export ./figures/Figure5_`var'.png, replace
}

